﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;
using BPERPHelpTool.Const;
using System.Windows.Forms;

namespace BPERPHelpTool.DBHelp
{
    class DataBaseHelp
    {

        //public static string sqlConn = "Data Source=192.168.48.2;Initial Catalog=CNLY_App;User ID=sa;Password=12345;MultipleActiveResultSets=True";

        //public SqlConnection GetDBConnection()
        //{
        //    return new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
        //}


        public SqlConnection GetCNLYDBConnection()
        {
            return new SqlConnection(BPSettings.Default.CNLYDatabaseConn);
        }
        public SqlConnection GetCNBTDBConnection()
        {
            return new SqlConnection(BPSettings.Default.CNBTDatabaseConn);
        }
        public SqlConnection GetCNP1DBConnection()
        {
            return new SqlConnection(BPSettings.Default.CNP1DatabaseConn);
        }
        public SqlConnection GetCNP2DBConnection()
        {
            return new SqlConnection(BPSettings.Default.CNP2DatabaseConn);
        }
        public SqlConnection GetUSMADBConnection()
        {
            return new SqlConnection(BPSettings.Default.USMADatabaseConn);
        }
        public SqlConnection GetTWHUDBConnection()
        {
            return new SqlConnection(BPSettings.Default.TWHUDatabaseConn);
        }


        public static DataSet PublicGetDataSet(string sqlCommand, string tableName)
        {
            SqlConnection conn = null;
            try
            {

                conn = new SqlConnection(BPSettings.Default.CNLYDatabaseConn);
                conn.Open();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand, conn);

                DataSet dataSet = new DataSet("UserDetailsTable");

                dataAdapter.Fill(dataSet);
                dataSet.Tables[0].TableName = tableName;
                return dataSet;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "QueryException", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                return null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public static DataSet GetDataSet(string sqlCommand, string tableName)
        {
            SqlConnection conn = null;

            string tn = tableName;
            if (tn == TableNameConst.CNLYRepTable)
            {
                conn = new SqlConnection(BPSettings.Default.CNLYDatabaseConn);
            }
            else if (tn == TableNameConst.CNBTRepTable)
            {
                conn = new SqlConnection(BPSettings.Default.CNBTDatabaseConn);
            }
            else if (tn == TableNameConst.CNP1RepTable)
            {
                conn = new SqlConnection(BPSettings.Default.CNP1DatabaseConn);
            }
            else if (tn == TableNameConst.CNP2RepTable)
            {
                conn = new SqlConnection(BPSettings.Default.CNP2DatabaseConn);
            }
            else if (tn == TableNameConst.USMARepTable)
            {
                conn = new SqlConnection(BPSettings.Default.USMADatabaseConn);
            }
            else if (tn == TableNameConst.TWHURepTable)
            {
                conn = new SqlConnection(BPSettings.Default.TWHUDatabaseConn);
            }
            try
            {
                //conn = new SqlConnection(BPSettings.Default.CNLYDatabaseConn);
                conn.Open();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand, conn);

                DataSet dataSet = new DataSet("Rep Table");

                dataAdapter.Fill(dataSet);
                dataSet.Tables[0].TableName = tableName;
                return dataSet;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "QueryException", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                return null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public static void RemoveData(ref DataSet ds, string columnName, int value)
        {
            List<DataRow> list = new List<DataRow>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int delayMin = int.Parse(dr[columnName].ToString());

                if (delayMin <= value)
                {
                    list.Add(dr);
                }
            }
            foreach (DataRow dr in list)
            {
                ds.Tables[0].Rows.Remove(dr);
            }
        }

        public static DataSet GetCNLYRepDataSet()
        {
            DataSet dsCNLYRep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.CNLYRepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsCNLYRep;
        }

        public static DataSet GetCNBTRepDataSet()
        {
            DataSet dsCNBTRep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.CNBTRepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsCNBTRep;
        }

        public static DataSet GetCNP1RepDataSet()
        {
            DataSet dsCNP1Rep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.CNP1RepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsCNP1Rep;
        }

        public static DataSet GetCNP2RepDataSet()
        {
            DataSet dsCNP2Rep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.CNP2RepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsCNP2Rep;
        }

        public static DataSet GetUSMARepDataSet()
        {
            DataSet dsUSMARep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.USMARepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsUSMARep;
        }

        public static DataSet GetTWHURepDataSet()
        {
            DataSet dsTWHURep = GetDataSet(BPSettings.Default.RepRowsErrorsScript, TableNameConst.TWHURepTable);
            //RemoveData(ref dsOrder, CFFConst.DelayMinute, BPSettings.Default.OrderWarningMin);
            return dsTWHURep;
        }

        public static DataSet GetUserDetailDataSet(string user, string depart)
        {

            string str = "testdddd '{0}'";
            str = string.Format(str, "dddd");

            string sqlstruser = @"use CNLY_App
select UN.Username as '用户名'
,case UN.SuperUserFlag  when 1 then N'超级用户' when 0 then N'普通用户' end as '用户类型'
,UN.UserDesc as '用户说明'
,case UN.[Status] when 0 then N'活动' when 1 then N'已锁定' when 2 then N'已禁用' end as '用户登录状态'
,ISNULL(UN.Uf_Department,'') as '部门'
,ISNULL(DP.[description],'')as '部门描述'
,ISNULL(case UN.EditLevel when 0 then N'无' when 1 then N'基本' when 2 then N'完全用户' when 3 then N'地点程序员' end,'') as '编辑权限'
,ISNULL(UE.EmailAddress,'') as '邮箱地址'
,UN.CreateDate as '创建日期'
--,UM.ModuleName
--,UN.[Status]
from UserNames UN
left join dept DP
on DP.dept = UN.Uf_Department
left join UserModules UM
on UM.UserId = UN.UserId
left join UserEmail UE
on un.Username = UE.Username 
and UN.NoteExistsFlag = UE.NoteExistsFlag
--where DP.dept = 'FA1555' --筛选部门
--and UM.ModuleName = 'SyteLineTrans'
--where UN.Username not in ( 'sa','SL_Internal','service','controller','repl_user')
where UN.[Status] in (0,1) 
--where UN.[Status] in (2) 
and UN.Username = '" + user + "' order by UN.Username ";

            //sqlstruser = string.Format(BPSettings.Default.SqlUserSearchFilterUser,user);
            DataSet dsUDD = null;
            if (user == "" & depart == "")
            {
                dsUDD = PublicGetDataSet(BPSettings.Default.SqlUserSearch, TableNameConst.UserDetailsTable);
            }
            else if (user != "" & depart == "")
            {
                dsUDD = PublicGetDataSet(sqlstruser, TableNameConst.UserDetailsTable);

            }
            return dsUDD;
        }


        public static int GetCNLYRepRow()
        {
            return GetCNLYRepDataSet().Tables[0].Rows.Count;
        }

        public static int GetCNBTRepRow()
        {
            return GetCNBTRepDataSet().Tables[0].Rows.Count;
        }

        public static int GetCNP1RepRow()
        {
            return GetCNP1RepDataSet().Tables[0].Rows.Count;
        }

        public static int GetCNP2RepRow()
        {
            return GetCNP2RepDataSet().Tables[0].Rows.Count;
        }

        public static int GetUSMARepRow()
        {
            return GetUSMARepDataSet().Tables[0].Rows.Count;
        }

        public static int GetTWHURepRow()
        {
            return GetTWHURepDataSet().Tables[0].Rows.Count;
        }

        public static DataTable GetUserDetailTable(string user, string depart)
        {
            return GetUserDetailDataSet(user, depart).Tables[0];
        }

    }
}
